﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using CrystalDecisions.CrystalReports.Engine;
using System.Collections;
using System.Data.Objects;
using System.Data.Objects.SqlClient;
using System.Data.Common;
using XBoshiGFS.forms;


namespace XBoshiGFS.reports
{
    public partial class PeopleInforQueryForm : Form
    {
        XBoshiGFSModelContainer context = new XBoshiGFSModelContainer();
        IQueryable  orgSql;
        String strGroupID;
 




        public PeopleInforQueryForm()
        {
            InitializeComponent();



            IList<StationSet> list = (from p in context.StationSet  select p).ToList();
            list.Insert(0, new StationSet() { Id = "", strName = "", strCode = "" });
            cmbStation.DataSource = list;

            //灌季
            cmbWaterSeason.DataSource = (from p in context.WaterSeasonSet  select p);

        }
 
 

        private void cmbStation_SelectedIndexChanged(object sender, EventArgs e)
        {
            var strID = ((ComboBox)sender).SelectedValue;

            IList<SegmentCourseSet> list = (from p in context.SegmentCourseSet where p.StationId == strID orderby p.strName select p).ToList();
            list.Insert(0, new SegmentCourseSet() {Id ="",strName ="",strCode ="" });
            cmbSegment.DataSource = list;
        }

        private void cmbSegment_SelectedIndexChanged(object sender, EventArgs e)
        {
            var strID = ((ComboBox)sender).SelectedValue;
            IList<WaterCourseSet> list = (from p in context.WaterCourseSet where p.SegmentCourseId == strID orderby p.strName select p).ToList();
            list.Insert(0, new WaterCourseSet() { Id = "", strName = "", strCode = "" });

            cmbWaterCourse.DataSource = list ;

        }

        private void cmbWaterCourse_SelectedIndexChanged(object sender, EventArgs e)
        {
            var strID = ((ComboBox)sender).SelectedValue;
        //    cmbVillage.DataSource = (from p in context.VillageSet where p.WaterCourseId == strID orderby p.strName select p);
            IList<VillageSet> list =(from p in context.VillageSet where p.WaterCourseId == strID orderby p.strName select p).ToList();
            list.Insert(0, new VillageSet() { Id = "", strName = "", strCode = "" });
            cmbVillage.DataSource = list;
        }

        private void cmbVillage_SelectedIndexChanged(object sender, EventArgs e)
        {
            var strID = ((ComboBox)sender).SelectedValue;
            IList<GroupSet> list = (from p in context.GroupSet where p.VillageId == strID orderby p.strName select p).ToList();
            list.Insert(0, new GroupSet() { Id = "", strName = "", strCode = "" });
            cmbGroup.DataSource = list;
        }
        ProcessBarForm pr =null;
        String strSql = "";
        private void btnQuery_Click(object sender, EventArgs e)
        {
            strSql = getSQLStr();
            pr = new ProcessBarForm();
         //   pr.Parent  = this;



            Action handler = new Action(queryData);
            handler.BeginInvoke(
                new AsyncCallback(this.AsyncCallback),
                handler
                );
            pr.StartTimer(50);
            pr.ShowDialog();

        }

        private void queryData() {
            try
            {


                var sql = GetQuerySql(strSql);
              //  waterBillReportDataBindingSource.DataSource = sql;

                System.Windows.Forms.MethodInvoker invoker = () => waterBillReportDataBindingSource.DataSource = sql;
                if (this.InvokeRequired)
                {
                    this.Invoke(invoker);
                }
                else
                {
                    invoker();
                }


            }
            catch (Exception ex)
            {

            } 
        
        }
        // 结束异步操作
        private void AsyncCallback(IAsyncResult ar)
        {
            // 标准的处理步骤
            Action handler = ar.AsyncState as Action;
            handler.EndInvoke(ar);
            System.Windows.Forms.MethodInvoker invoker = () => pr.Dispose();
            if (this.InvokeRequired)
            {
                this.Invoke(invoker);
            }
            else
            {
                invoker();
            }


        }

        private String getSQLStr() {
            String strPeopleName = TBName.Text;
            String strPeopleCode = TBCode.Text;
            String strStationID = (String)cmbStation.SelectedValue;
            String strSegementID = (String)cmbSegment.SelectedValue;
            String strWaterCourseID = (String)cmbWaterCourse.SelectedValue;
            String strVillageID = (String)cmbVillage.SelectedValue;
            String strGroupID = (String)cmbGroup.SelectedValue;

            String strSQl =
            " SELECT  " +
            " 0.0 AS decPrice, " +
            " t.UseArea AS decUseArea, " +
            " p.Id AS strGropId, " +
            " o.strCode AS strGroupCode, " +
            " o.strManager AS strGroupManager, " +
            " o.strName AS strGroupName, " +
            " t.strCode AS strPeopleCode, " +
            " t.Id AS strPeopleID, " +
            " t.strName AS strPeopleName, " +
            " r.strCode AS strSegmentCode, " +
            " r.strName AS strSegmentName, " +
            " s.strCode AS strStationCode, " +
            " s.strName AS strStationName, " +
            " p.strCode AS strVillageCode, " +
            " p.strName AS strVillageName, " +
            " q.strCode AS strWaterCourseCode, " +
            " q.strName AS strWaterCourseName, " +
            " q.strName AS strWaterSeasonName "
            + "  FROM      PeopleSet AS t INNER JOIN "
            + "   GroupSet AS o ON t.GroupId = o.Id INNER JOIN "
            + "  VillageSet AS p ON o.VillageId = p.Id INNER JOIN "
            + "    WaterCourseSet AS q ON p.WaterCourseId = q.Id INNER JOIN "
            + "    SegmentCourseSet AS r ON q.SegmentCourseId = r.Id INNER JOIN "
            + "    StationSet AS s ON r.StationId = s.Id "
            + "  where 1=1 ";
            if (strPeopleName != "")
            {
                strSQl = strSQl + " AND t.strName LIKE ('%" + strPeopleName + "%') ";
            }
            if (strPeopleCode != "")
            {
                strSQl = strSQl + " AND t.strCode LIKE ('%" + strPeopleCode + "%') ";
            }
            if (strStationID != "")
            {
                strSQl = strSQl + " AND s.Id ='" + strStationID + "' ";
            }
            if (strSegementID != "")
            {
                strSQl = strSQl + " AND r.Id ='" + strSegementID + "' ";
            }
            if (strWaterCourseID != "")
            {
                strSQl = strSQl + " AND q.Id ='" + strWaterCourseID + "' ";
            }
            if (strVillageID != "")
            {
                strSQl = strSQl + " AND p.Id ='" + strVillageID + "' ";
            }
            if (strGroupID != "")
            {
                strSQl = strSQl + " AND o.Id ='" + strGroupID + "' ";
            }
            return strSQl;
        
        
        
        }


        private IList<WaterBillReportData> GetQuerySql(String strSQl)
        {

  
            var query = context.CreateQuery<DbDataRecord>(strSQl);
            IList<WaterBillReportData> list = new List<WaterBillReportData>();
            foreach (var rs in query )
        {
            try
           {
                WaterBillReportData wb = new WaterBillReportData();
                wb.decPrice = decimal.Parse (rs["decPrice"].ToString()) ;
                wb.decUseArea = rs["decUseArea"] is DBNull ? 0.0M : decimal.Parse(rs["decUseArea"].ToString());
                wb.strGropId = rs["strGropId"] is DBNull ? "" : (String)rs["strGropId"];
                wb.strGroupCode = rs["strGroupCode"] is DBNull ? "" : (String)rs["strGroupCode"];
                wb.strGroupManager = rs["strGroupManager"] is DBNull ? "" : (String)rs["strGroupManager"];
                wb.strGroupName = rs["strGroupName"] is DBNull ? "" : (String)rs["strGroupName"];
                wb.strPeopleCode = rs["strPeopleCode"] is DBNull ? "" : (String)rs["strPeopleCode"];
                wb.strPeopleID = rs["strPeopleID"] is DBNull ? "" : (String)rs["strPeopleID"];
                wb.strPeopleName = rs["strPeopleName"] is DBNull ? "" : (String)rs["strPeopleName"];
                wb.strSegmentCode = rs["strSegmentCode"] is DBNull ? "" : (String)rs["strSegmentCode"];
                wb.strSegmentName = rs["strSegmentName"] is DBNull ? "" : (String)rs["strSegmentName"];
                wb.strStationCode = rs["strStationCode"] is DBNull ? "" : (String)rs["strStationCode"];
                wb.strStationName = rs["strStationName"] is DBNull ? "" : (String)rs["strStationName"];
                wb.strVillageCode = rs["strVillageCode"] is DBNull ? "" : (String)rs["strVillageCode"];
                wb.strVillageName = rs["strVillageName"] is DBNull ? "" : (String)rs["strVillageName"];
                wb.strWaterCourseCode = rs["strWaterCourseCode"] is DBNull ? "" : (String)rs["strWaterCourseCode"];
                wb.strWaterCourseName = rs["strWaterCourseName"] is DBNull ? "" : (String)rs["strWaterCourseName"]; 
                list.Add(wb); 
            }
            catch { 
            
            }

        }
            return list; 
        } 


        private void cmbGroup_SelectedIndexChanged(object sender, EventArgs e)
        {
    
        }

        private void cmbWaterSeason_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.btnQuery.Enabled = true;
        }

        private void WaterBillReportForm_Load(object sender, EventArgs e)
        {

        }

        private void tableLayoutPanel1_Paint(object sender, PaintEventArgs e)
        {

        }




        private void btnReset_Click(object sender, EventArgs e)
        {
            cmbStation.SelectedValue = "";
            TBCode.Text = "";
            TBName.Text = "";
        }



        private void waterBillReportDataDataGridView_SelectionChanged(object sender, EventArgs e)
        {
           //更新从表信息
            BindingSource bins = (BindingSource)waterBillReportDataDataGridView.DataSource;
            WaterBillReportData wbrd = (WaterBillReportData)bins.Current;
            var wus = (from p in context.WaterUsageSet where p.PeopleId == wbrd.strPeopleID  orderby p.BeginTime descending  select p);
            waterUsageSetBindingSource.DataSource = wus.ToList();  
        }

        private void splitter1_SplitterMoved(object sender, SplitterEventArgs e)
        {

        }

        private void btnExport_Click(object sender, EventArgs e)
        {
          //  ExportToExcelHelper.DataGridViewToExcel(waterBillReportDataDataGridView);
        }

        private void waterBillReportDataDataGridView_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
           // base.OnRowPostPaint(e); 
            e.Graphics.DrawString((e.RowIndex + 1).ToString(), e.InheritedRowStyle.Font,new SolidBrush(Color.Black),
                e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + 5);
        }





    }
}
